Understanding Schema and Reference Tables

Schema and Reference Tables play a critical role in defining how data quality rules are validated in a DQ Processor job. They provide structure, context, and reliable values that rules rely on during validation. You can define rules that validate not only individual columns and values, but also the structure of a dataset and its relationships with other datasets.

These validations are enabled through schema rules and reference table–based rules, which use reference datasets to compare schemas, row counts, or data relationships.

This topic explains how Schema and Reference Tables are used during Data Quality rule execution with practical examples to help you understand and configure them correctly.

What Is a Schema?

A schema represents the expected structure of your data including column names, data types, nullability, and constraints. In data quality processing, schemas are used to:

• Validate whether incoming data matches the expected structure

• Detect missing, extra, or mismatched columns

• Enforce data type and format consistency

Example: Schema Validation

Expected Schema

Column Name	Data Type	Allow Null Values
customer_ID	Integer	No
email	String	No
country_code	String	Yes
created_date	Date	No

 

Issues with incoming data

The incoming data shows the following issues:

• customer_id arrives as STRING instead of INT

• created_date is missing

• extra column temp_flag is present

Schema-based rules detect these structural issues before deeper validations run.

What is a reference table?

A reference table is a trusted, reliable dataset used to validate values in your source data. It is an external dataset that acts as a trusted baseline during data quality evaluation.

Reference tables are typically:

• Master data tables (countries, currencies, products, customers, accounts)

• Lookup tables

• Historical snapshots

• Canonical or certified datasets

• Business-controlled validation datasets

• Upstream or downstream pipeline outputs

In the DQ Processor, reference tables are used to:

• Validate schema consistency

• Compare dataset row counts

• Ensure referential integrity across datasets

• Verify dataset equivalence after transformation

Once configured, these tables can be referenced in rules using a defined dataset alias.

Why Reference Tables Matter in Data Quality

Reference tables help answer questions like:

• Is this value allowed?

• Does this record exist in the master system?

• Is the relationship between fields valid?

They enable context-aware validation and structural checks.

Example 1: Country Code Validation

Source Data

Customer_ID	Country_Code
101	USA
102	IND
103	XX

 

Reference Table: ref_country_code

Country_code	Country_Name
USA	United States of America
IND	India
UK	United Kingdom

 

Validation Rule: country_code must exist in ref_country_codes

Outcome: Record with XX fails validation.

Example 2: Product - Category Relationship

Source Data

Product ID	Category
P1001	Electronics
P1002	Apparel
P1003	Furniture

 

Reference Table: ref_product_category

Product ID	Allowed Category
P1001	Electronics
P1002	Apparel

 

Outcome: P1003 fails because it does not exist in the reference table.

Schema-Based Validation

SchemaMatch Rule

 

Purpose

Ensures that the schema of the processed dataset matches the schema of a reference dataset.

When to use

• Detect schema drift during ingestion or transformation

• Validate that new pipeline versions preserve the expected structure

• Enforce compatibility between source and target datasets

What is validated

• Column Names

• Data Types

• Column Order (wherever applicable)

Example Use Case

A processor reads daily customer data. You want to ensure that today’s dataset matches the approved schema from a certified baseline table.

Rule Example

Rules = [

Dataset.ref.SchemaMatch

]

Outcome

• Rule passes if both schemas match exactly

• Rule fails if columns are missing, added, renamed, or have incompatible data types

Reference-Based Data Validation

ReferentialIntegrity Rule

 

Purpose

Validates that values in a column exist in a corresponding column of a reference table.

When to use

• Enforce foreign key–like relationships

• Detect orphan or invalid reference values

• Ensure transactional data aligns with master data

Example Use Case

Ensure every customer_id in the orders dataset exists in the customers reference table.

Rule Example

Rules = [

ReferentialIntegrity "customer_id" "ref.customer_id"

]

Outcome

• Rule passes if all values are found in the reference table

• Rule fails if unmatched or invalid references are detected

DatasetMatch Rule

Purpose

Checks whether the processed dataset matches a reference dataset at a dataset level.

When to use

• Validate ETL or replication accuracy

• Compare transformed data with source or snapshot datasets

• Verify end-to-end pipeline correctness

Example Use Case

Compare post-processing data with a staging snapshot to ensure no data loss or alteration.

Rule Example

Rules = [

Dataset.ref.DatasetMatch

]

Outcome

• Rule passes when datasets are equivalent

• Rule fails if discrepancies exist in records or values

RowCountMatch Rule

Purpose

Ensures that the number of records in the processed dataset matches the reference dataset.

When to use

• Quick sanity check for ingestion completeness

• Early detection of missing or extra records

Example Use Case

Validate that all records from the source system were processed.

Rule Example

Rules = [

Dataset.ref.RowCountMatch

]

Outcome

• Rule passes if row counts are equal

• Rule fails if counts differ

Schema vs Reference Tables: Key Differences

Aspect	Schema	Reference Table
Purpose	Validates structure	Validates values
Focus	Columns, types, format	Allowed or trusted data
Typical Rules	Data type, nullability	Lookup, existence, consistency
Changes Over Time	Infrequent	Can change regularly
Example	Column must be DATE	Country code must be valid

How Schema and Reference Tables Are Used Together

In most real-world scenarios, both are used in combination.

Example Combined Flow

1. Schema Validation

   • Ensure required columns exist

   • Validate data types

2. Reference Validation

   • Validate codes, IDs, and relationships

3. Analyzer Rules

   • Assess completeness, uniqueness, distribution

This layered approach ensures:

• Structural correctness

• Business correctness

• Analytical reliability

How Reference Tables are Used in Rule Configuration

During the Rule Configuration step of the Data Quality Processor:

1. Reference tables are selected or mapped in advance.

2. Each reference table is assigned an alias (for example, ref).

3. Rules use this alias to access schema or column values from the reference dataset.

4. Rules are evaluated at runtime as part of the processor execution.

Common Processor Scenarios

Scenario	Recommended Rule
Validate schema stability across pipeline runs	SchemaMatch
Enforce master-transaction consistency	ReferentialIntegrity
Verify ETL transformation accuracy	DatasetMatch
Detect missing or extra records early	RowCountMatch

 

Best Practices

Use schemas to catch early structural issues

• Use reference tables for business-rule enforcement

• Keep reference tables small, curated, and reliable

• Version reference tables when business rules change

• Avoid hardcoding values in rules when a reference table can be used

• Use certified or trusted datasets as reference tables.

• Combine schema and reference rules with column-level rules for comprehensive validation.

• Apply reference-based rules in processor stages where cross-dataset validation is required.

• Review rule failures early to prevent downstream data issues.

Recommended Topics

What's next? Data Quality Processor Dashboards